# -*- encoding:utf-8 -*-
import json
from flask import Blueprint, g,session
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from lib.table import table
from config import config
from lib.record import recodelog
from lib import auth
from lib.funcs import compileStr
from lib.validform import V, ValidateForm
app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 获取用户信息
@app.route('/api/user/message', methods=['POST'])
@auth.permission("user", "readOnly")
def get_message():

    try:
        tb = table()
        g.cursor.execute("""select count(distinct a.person_id) as num
               from user_infos as a %s
               """ % (tb.where(
            ['a.person_name', 'a.agency_name', 'a.user_group_name'],
            "a.user_state = '1'")))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""select  a.person_id , a.person_name,
               a.email , a.mobile_phone,a.data_source,
               a.user_name,a.agency_name,a.person_id,a.remark,
               d2t(a.r_time), a.r_person,a.agency_id,a.email,a.mobile_phone,
               string_agg(distinct a.user_group_name,',') as user_group_name,string_agg(a.user_group_id,',') as user_group_id
               from user_infos as a %s
               group by a.person_id, a.person_name,
               a.email , a.mobile_phone,a.data_source,
               a.user_name,a.agency_name,a.person_id,a.remark,
               d2t(a.r_time), a.r_person,a.agency_id,a.email,a.mobile_phone
               %s %s %s""" % (tb.where(
            ['a.person_name', 'a.agency_name', 'a.user_group_name'],
            "a.user_state = '1'"), tb.orderBy(), tb.offset(), tb.limit()))
        row = g.cursor.fetchall()
        flag,row,msg = compileStr(row,['email', 'mobile_phone', 'person_name'])
        return json.dumps({
            "status": "success",
            "msg": {
                "current": tb.current(),
                "total": total,
                "dataSource": row
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': str(e)})


# 获取单个信息
@app.route('/api/user/personal', methods=['POST'])
@auth.permission("user", "readOnly")
def personal():
    try:
        form = ValidateForm(
            person_id=[
                '账号id', V.required()
            ], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """select  a.person_name,a.person_type,
               a.email , a.mobile_phone,a.data_source,
               a.user_name,a.person_name,a.person_id,a.remark,
               string_agg(distinct a.user_group_id,',') as user_group_id,
               string_agg(distinct a.user_group_name,',') as user_group_name,
               d2t(a.r_time), a.r_person,a.agency_id,a.agency_name,a.email,a.mobile_phone,sl.login_id,
               string_agg(distinct sr.role_name,',') as role_name,string_agg(distinct srf.group_id,',') as role_group_id
               from user_infos as a
               left join (select agency_name,agency_id,parent_agency_id from sys_agency_info where node_type_id = '0') as b on a.agency_id = b.parent_agency_id
               left join sys_person as sp on sp.person_id = a.person_id
               left join sys_login as sl on sl.person_id = sp.person_id
               left join sys_login_group as slg on slg.login_id = sl.login_id
               left join (select user_group_id from sys_user_group where state='1')as sug on sug.user_group_id = slg.user_group_id
               left join sys_usergroup_role as sur on sur.user_group_id = sug.user_group_id
               left join (select role_id,role_name from sys_role where state = '1') as sr on sr.role_id = sur.role_id
               left join sys_role_function as srf on srf.role_id = sr.role_id
               where a.person_id = %(person_id)s
               group by a.person_name,
               a.email , a.mobile_phone,a.data_source,
               a.user_name,a.person_id,a.remark,a.person_type,
               d2t(a.r_time), a.r_person,a.agency_id,a.agency_name,a.email,a.mobile_phone,sl.login_id
               """, (data))
        row = g.cursor.fetchall()
        flag,row,msg = compileStr(row,['email', 'mobile_phone', 'person_name'])
        return json.dumps({"status": "success", "msg": row})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': str(e)})


# 获取筛选条件下的person_id
@app.route('/api/group/id', methods=['POST'])
@auth.permission("user", "readOnly")
def get_person_id():
    try:
        form = ValidateForm(user_group_id=['用户组id', V.required()], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """select distinct sp.person_id from sys_login_group as slg
               left join sys_login as sl on sl.login_id = slg.login_id
               left join sys_person as sp on sp.person_id = sl.person_id
               where slg.user_group_id in (%s) and sp.state ='1' and sl.state = '1'
            """ %
            data['user_group_id'].replace('\"', '\'')[1:][:-1])
        person_id = g.cursor.fetchall()
        return json.dumps({"status": "success", "msg": person_id})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败'})


# 添加用户信息
@app.route('/api/user/add', methods=['POST'])
@auth.permission("user")
def add_user(_currUser):
    g.cursor.execute("SELECT public.get_location_seq('sys_person')")
    person_id = g.cursor.fetchone()
    g.cursor.execute("SELECT public.get_location_seq('sys_login')")
    login_id = g.cursor.fetchone()
    from lib import funcs
    md5_word = funcs.calc_md5(config.defaultPassword)
    try:
        form = ValidateForm(
            person_name=['用户姓名', V.required(type='base64')],
            user_name=[
                '账号',
                V.required(type='base64'),
                V.rangelength(3, 20),
                V.pattern(r'^[a-zA-Z0-9_]+$', '由字母数字下划线组成')
            ],
            node_id=['机构', V.required(type='base64')],
            user_group_id=['用户组id', V.optional(type='base64')],
            email=['邮箱地址', V.optional(type='base64')],
            mobile_phone=['手机号码', V.optional(type='base64'),V.number()],
            remark=['备注', V.optional(type='base64')]
        )

        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """insert into sys_person(person_type,person_name,node_id,person_id,email,mobile_phone,remark,state,data_source,r_person)
                values('0',%s,%s,
                %s,%s,%s,%s,1,1,%s)""" , (data['person_name'], data['node_id'], person_id['get_location_seq'], data['email'], data['mobile_phone'], data['remark'], _currUser['user']['person_name']))
        g.cursor.execute(
            """insert into sys_login(user_name,person_id,login_id,user_password,data_source)
               values(%s,%s,%s,%s,'1')""" ,
            (data['user_name'], person_id['get_location_seq'], login_id['get_location_seq'], md5_word))
        for items in json.loads(data['user_group_id']):
            g.cursor.execute(
                """insert into sys_login_group(login_id,user_group_id,data_source)
                                values(%s,%s,'1')""" ,
                (login_id['get_location_seq'], items))
        g.cursor.execute(
            """insert into sys_user_theme (person_id,left_navigation,head_navigation,fixed_footer,theme_colour,r_person)
                            values(%s,'1','1','1','blue',%s)""" ,
            (person_id['get_location_seq'], _currUser['user']['person_name']))
        g.conn.commit()
        if len(json.loads(data['user_group_id'])) > 0:
            g.cursor.execute("""select string_agg(user_group_name,',') as user_group_name from sys_user_group where user_group_id in (%s)""" % data['user_group_id'].replace('\"', '\'')[1:][:-1])
            user_group_name = g.cursor.fetchone()['user_group_name']
        else:
            user_group_name = ''
        g.cursor.execute("""select node_name from sys_node_info where node_id = '%s'""" % data['node_id'])
        node_name = g.cursor.fetchone()['node_name']
        g.cursor.execute("""select person_name from sys_person where person_id = '%s'""" % person_id['get_location_seq'])
        person_name = g.cursor.fetchone()['person_name']
        rem = {
            "用户姓名": "%s" % person_name,
            "账号": "%s" % data['user_name'],
            "机构": "%s" % node_name,
            "用户组": "%s" % user_group_name,
            "邮箱": "%s" % data['email'],
            "电话": "%s" % data['mobile_phone'],
            "备注": "%s" % data['remark']
        }
        auth.logsync(_currUser, {
            "function": "用户管理",
            "type": "新增",
            "remark": "新增用户信息:" + recodelog(rem)
        })
        return json.dumps({"status": "success", "msg": "添加用户成功"})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': "添加用户失败"})


# 对新建用户账号的检测
@app.route('/api/user/jiance', methods=['POST'])
@auth.permission("user", "readOnly")
def jiance_id():
    try:
        form = ValidateForm(
            auser_name=[
                '账号',
                V.required(),
                V.rangelength(3, 20),
                V.pattern(r'^[a-zA-Z0-9_]+$', '由字母数字下划线组成')
            ], )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """select user_name from sys_login where user_name = %(auser_name)s and state='1'""",
            (data))
        user_name = g.cursor.fetchone()
        if user_name:
            return json.dumps({"status": "error", "msg": "账号已存在"})
        else:
            return json.dumps({"status": "success", "msg": "可以注册"})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败'})


# 修改信息
@app.route('/api/user/update', methods=['POST'])
@auth.permission("user")
def user_update(_currUser):
    try:
        form = ValidateForm(
            person_name=['用户姓名', V.optional(type='base64')],
            user_name=['账号', V.optional(type='base64')],
            agency_id=['机构', V.required(type='base64')],
            user_group_id=['用户组id', V.optional(type='base64')],
            person_id=['用户id', V.required(type='base64')],
            email=['邮箱地址', V.optional(type='base64')],
            mobile_phone=['手机号码', V.optional(type='base64'),V.number()],
            remark=['备注', V.optional(type='base64')],
            r_person=['记录人', V.required(type='base64')],
            login_id=['账号ID', V.required(type='base64')]
            
        )

        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """update sys_person set email = %(email)s,person_name = %(person_name)s,r_person=%(r_person)s,
            mobile_phone = %(mobile_phone)s, remark = %(remark)s, agency_id = %(agency_id)s
            where  person_id = %(person_id)s""", (data))
        g.cursor.execute(
            """
                delete from sys_login_group where  login_id = '%s'
            """ % (data['login_id']))
        g.conn.commit()
        for items in json.loads(data['user_group_id']):
            g.cursor.execute(
                """insert into sys_login_group(login_id,user_group_id,data_source)
                                values('%s','%s','1')""" %
                (data['login_id'], items))
        g.conn.commit()
        if len(json.loads(data['user_group_id'])) > 0:
            g.cursor.execute("""select string_agg(user_group_name,',') as user_group_name from sys_user_group where user_group_id in (%s)""" % data['user_group_id'].replace('\"', '\'')[1:][:-1])
            user_group_name = g.cursor.fetchone()['user_group_name']
        else:
            user_group_name = ''
        g.cursor.execute("""select node_name from sys_agency_info where node_id = '%s'""" % data['node_id'])
        agency_name = g.cursor.fetchone()['agency_name']
        g.cursor.execute("""select person_name from sys_person where person_id = '%s'""" % data['person_id'])
        person_name = g.cursor.fetchone()['person_name']
        if _currUser['user']['user_name'] == 'user_name':
            _currUser['user']['person_name'] = data['person_name']
            _currUser['user']['email'] = data['email']
            _currUser['user']['mobile_phone'] = data['mobile_phone']
            _currUser['user']['remark'] = data['remark']
            _currUser['user']['department_name'] = agency_name
            _currUser['user']['agency_id'] = data['agency_id']
            session['_currUser'] = _currUser
        rem = {
            "用户姓名": "%s" % person_name,
            "机构": "%s" % agency_name,
            "用户组": "%s" % user_group_name,
            "邮箱": "%s" % data['email'],
            "电话": "%s" % data['mobile_phone'],
            "备注": "%s" % data['remark'],
            "记录人": "%s" % data['r_person']
        }
        auth.logsync(_currUser, {
            "function": "用户管理管理",
            "type": "修改",
            "remark": "修改用户信息:" + recodelog(rem)
        })
        return json.dumps({"status": "success", "msg": "修改信息成功"})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': "修改信息失败"})


# 用户禁用
@app.route('/api/user/stop', methods=['POST'])
@auth.permission("user")
def stop_login(_currUser):
    try:
        form = ValidateForm(
            person_id=['用户id', V.required()], r_person=['记录人', V.required()], person_name=['用户名', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute(
            """update sys_person set state = '0',r_person=%(r_person)s where  person_id = %(person_id)s  """,
            (data))
        g.cursor.execute(
            """update sys_login set state = '0' where  person_id = %(person_id)s """,
            (data))
        g.conn.commit()
        rem = {
            "用户ID": "%s" % data['person_id'],
            "记录人": "%s" % data['r_person'],
            "用户名": "%s" % data['person_name'],
        }
        auth.logsync(_currUser, {
            "function": "用户管理",
            "type": "删除",
            "remark": "删除用户信息:" + recodelog(rem)
        })
        return json.dumps({"status": "success", 'msg': '删除成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': "删除用户失败"})


# 同步信息数量接口
@app.route('/api/user/snyccount', methods=['POST'])
@auth.permission("user", "readOnly")
def snyccount(_currUser):
    try:
        g.cursor.execute(
            """select count(table_id) from h_synchroniz_info where person_id = '%s' and state ='1' and synchroniz_table = 'sys_person' """ % (_currUser['user']['person_id']))
        count = g.cursor.fetchone()
        return json.dumps({"status": "success", "msg": count})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': str(e)})


# 已查看同步信息
@app.route('/api/user/delsnyccount', methods=['POST'])
@auth.permission("user", "readOnly")
def delsnyccount(_currUser):
    try:
        g.cursor.execute(
            """update h_synchroniz_info set state = '0' where person_id = '%s' and synchroniz_table = 'sys_person' """ % (_currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({"status": "success", "msg": '信息删除成功'})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '信息删除失败'})


# 同步信息
@app.route('/api/user/snycmessage', methods=['POST'])
@auth.permission("user", "readOnly")
def snycmessage(_currUser):
    try:
        tb = table()
        _where = """a.synchroniz_table = 'sys_person' and a.person_id = '%s'
                    and a.state = '1' and a.operating = '%s'""" % (_currUser['user']['person_id'], tb.dict['type'])
        g.cursor.execute("""SELECT z.login_id as user_name,z.person_id_change as person_id,z.user_name_change as person_name,
                            z.user_group_change as user_group_name,z.user_node_change as agency_name,z.email_change as email,z.phoneno_change as mobile_phone,d2t(z.r_time) as zr_time
                            from h_synchroniz_info a
                            left join h_login_synchroniz_info z on a.r_time = z.r_time
                            %s %s""" % (tb.where(['z.user_name_change', 'z.user_node_change', 'z.person_id_change'], _where), tb.orderBy()))
        return json.dumps({
            "status": "success",
            "data": {
                "dataSource": g.cursor.fetchall()
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取同步信息失败'})


@app.route('/api/user/synclog', methods=['POST'])
@auth.permission('user', "readOnly")
def synclog(_currUser):
    """查看 同步历史 - 信息"""
    try:
        tb = table()
        _where = """a.synchroniz_table = 'sys_person' and a.person_id = '%s'
                    and a.state = '0' and a.operating = '%s'""" % (_currUser['user']['person_id'], tb.dict['type'])

        g.cursor.execute("""SELECT count(1) as num
                            from (SELECT z.login_id as user_name,z.person_id_change as person_id,z.user_name_change as person_name,
                                z.user_group_change as user_group_name,z.user_node_change as node_name,z.email_change as email,z.phoneno_change as mobile_phone,d2t(z.r_time) as zr_time
                                from h_synchroniz_info a
                                left join h_login_synchroniz_info z on a.r_time = z.r_time
                                %s) a
                            """ % (tb.where(['z.user_name_change', 'z.user_node_change', 'z.person_id_change'], _where)))
        total = g.cursor.fetchone()["num"]

        g.cursor.execute("""SELECT z.login_id as user_name,z.person_id_change as person_id,z.user_name_change as person_name,
                            z.user_group_change as fuser_group_name,z.user_node_change as node_name,z.email_change as email,z.phoneno_change as mobile_phone,d2t(z.r_time) as zr_time
                            from h_synchroniz_info a
                            left join h_login_synchroniz_info z on a.r_time = z.r_time
                            %s %s %s %s""" % (tb.where(['z.user_name_change', 'z.user_node_change', 'z.person_id_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))

        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": g.cursor.fetchall()
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '获取同步历史信息失败'})


# 获取机构信息
@app.route('/api/user/node', methods=['POST'])
@auth.permission("user", "readOnly")
def user_mechanism_node():
    try:
        form = ValidateForm(
            aagency_id=['机构名称', V.optional()],
            type=['需要类型', V.required()],
            belong_area=['区域名称', V.optional()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        if data['type'] == 'monitoring':
            if data['belong_area']:
                g.cursor.execute(""" select  agency_name,agency_id from sys_node_info where state = '1' and belong_area = %(belong_area)s""", (data))
            else:
                g.cursor.execute(""" select  agency_name,agency_id from sys_node_info where state = '1' """)
        else:
            g.cursor.execute(
                """ select agency_name,agency_id from sys_agency_info where agency_id not in (with RECURSIVE node_info as
                    (
                    select agency_id from sys_agency_info where agency_id=%(aagency_id)s
                    union all
                    select s.agency_id from sys_agency_info s inner join node_info n on s.parent_agency_id = n.agency_id
                    )
                    select agency_id from node_info) and node_type_id ='1' and state= '1'""", (data))
        msg = g.cursor.fetchall()
        return json.dumps({"status": "success", 'msg': msg})
    except Exception, e:
        return json.dumps({
            "status": "except",
            'msg': str(e)
        })


# # 获取部门选项
# @app.route('/api/user/fdepartment', methods=['GET'])
# @auth.permission("user", "readOnly")
# def user_fdepartment_name():
#     try:
#         g.cursor.execute(
#             """select agency_id, agency_name as value ,parent_agency_id from sys_agency_info where state = '1'"""
#         )
#         rows = g.cursor.fetchall()
#         first = {}
#         second = {}
#         for row in rows:
#             if row.get("parent_agency_id") is None:
#                 continue
#             if not first.get(row.get("parent_agency_id"), []):
#                 first[row.get("parent_agency_id")] = []
#             first[row.get("parent_agency_id")].append(row.get("agency_id"))
#             second[row.get("agency_id")] = row
#         for row in rows:
#             row['label'] = row['value']
#             if row.get("parent_agency_id") is None:
#                 continue
#             for m in first.get(row.get("agency_id", ""), []):
#                 if not row.get('children'):
#                     row["children"] = []
#                 row["children"].append(second.get(m))
#         rows = filter(lambda m: bool(m.get("parent_agency_id") == '0'), rows)
#         g.cursor.execute("""select agency_id from sys_agency_info where state='1'""")
#         key = g.cursor.fetchall()
#         return json.dumps({"status": "success", 'msg': rows, 'key': key})
#     except Exception, e:
#         return json.dumps({"status": "except", 'msg': '获取信息失败'})